import requests
import json
import pymysql
import xlrd
import time

conn = pymysql.connect(host='192.168.2.205', port=3306, user='jinhuitao', password='jinhuitao_123456',
                       database='sunway_db', charset='utf8')
cursor = conn.cursor()


def excel_s():
    books = xlrd.open_workbook(r"D:/dhl.xlsx")

    sheet = books.sheet_by_index(0)  # sheet_by_index(0)表示为ceshishujvku.xlsx中的第一个表单
    print(f"第一列的内容是:{sheet.col_values(colx=0)}")

    return sheet.col_values(colx=0)


def mysql_s(times, title, describes, Tracking_number, pIds):
    newtime = time.strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute(
        "REPLACE into load_dhl set newtime=%s,times=%s,title=%s,describes=%s,Tracking_number=%s,pIds=%s",
        [newtime, times, title, describes, Tracking_number, pIds])

    conn.commit()


def run():
    for j in excel_s():
        print(str(j)[:-2])
        url = 'https://mydhl.express.dhl/shipmentTracking?AWB={}&countryCode=hk&languageCode=zh'.format(str(j)[:-2])
        headers = {
            'Accept': 'application/json, text/plain, */*',
            'Accept-Encoding': 'gzip, deflate, br',
            'Accept-Language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
            'Connection': 'keep-alive',
            # 'Cookie': 'ewfSessionId=1478297327159025664; BIGipServer~EWF~pl_internal-ewf-cbj.dhl.com_1443=4288817351.41733.0000; OptanonAlertBoxClosed=2022-01-04T09:29:32.457Z; s_ccst={%22target%22:true%2C%22ecid%22:true%2C%22aa%22:true}; AMCVS_9D88879D5579828F7F000101%40AdobeOrg=1; s_cc=true; AKA_A2=A; bm_sz=44D07E5C8B47164FCC3CF691650E0FF0~YAAQlPffF25heaJ9AQAAvQGJKQ4HHf6UCH5ppImtwFMD47Z/YPznrf8+9G8TVCMrF8eHLpMgwAg0D3tx4NBK9sIConIieDbL/CYNyBoWosBIXAXGNeIC5ME5lT1OX4YB56yu916FgwfglXHr3lw4G6MA2E9DUVyRaMlhHCkUY6Sp+hRoACXW9pfdJbQDxuPz4S25gex8XQr6Oa989F/pMG0aOzIkYuIdR8xUOFRxI5iUN8XB60QED5sBm7bJv7DJ3jZdXOsfnMQo+aYZktRhzT7sTS6NP8B/qh4Gphf+I+PRg/D0~4405559~4407863; TS0136675b=01914b743d6c92b6d0e74b471ede10eb13bef80773e8520b5ab20b620f4baa32a9371d75f072e4319f642c52cdd5183b48aa319672; RT="z=1&dm=express.dhl&si=jnqhcsbbrh&ss=kxzwt433&sl=0&tt=0"; ak_bmsc=FC45B8E207F03F6D387FE062DB260EEB~000000000000000000000000000000~YAAQlPffF9pheaJ9AQAArQuJKQ6Hw7wtUPaEqV7WG9caOFcf/iT++Qgvp4Ty06nicgCWzmCu/iTKc0XzfORs7QsT312epz0BbjL6o3HOhmfegKnTikCdyy4eTjr0zlokrUXTELwJk3EOIIEkMNSr40y+HbhQBTtAkm47Tz1WvVVI1wiNCk0vboHqqvXPiiKSSY+p5uX1sf3sls2LY1Fp3EzMDwKcbift6UcUIJ+o77rvWS1uoZ4nc+FGCQSJHb7IqziQbrOUrqMWsCGg1QIjtS2q5oPVIJt5wR3Tvf9ecFLdnwyRHGZvFJciht1Omz944Uya6U0C/U/71rgM8XpCpCat6NPNF4nqtMZljCcbfQvgL9PxDeO7dho30qKcz1DqQuhCTEFYKkdKaZp9xmMDnPLdMfLVQiEg9D0R3187tmHVYFrJ6L2V32goCbCmxqEf+8XU5z9Q3z8Zw5GTTJfIHpqSVD07yaWPDXCjr/GKMw==; OptanonConsent=isGpcEnabled=0&datestamp=Wed+Jan+05+2022+17%3A19%3A14+GMT%2B0800+(%E4%B8%AD%E5%9B%BD%E6%A0%87%E5%87%86%E6%97%B6%E9%97%B4)&version=6.26.0&isIABGlobal=false&consentId=419437aa-265f-4cf7-86ef-96cb4b583f3a&interactionCount=1&landingPath=NotLandingPage&groups=C0001%3A1%2CC0002%3A1%2CC0003%3A1%2CC0004%3A1%2CYOU01%3A1&hosts=H290%3A1%2CH224%3A1%2CH223%3A1%2CH222%3A1%2CH292%3A1%2CH293%3A1%2CH291%3A1%2CH214%3A1%2CH221%3A1%2CH44%3A1%2CH295%3A1%2CH2%3A1%2CH21%3A1%2CH175%3A1%2CH29%3A1%2CH38%3A1&geolocation=%3B&AwaitingReconsent=false; _abck=E40B652D5C743577A1DEC1A8EE280F02~0~YAAQlPffFxZieaJ9AQAABBGJKQc2c9guw85YQbwhdIcRt1QgPlHTNomx2GJoHqnsI6C483bIHRYDgVvUs8oYbrHvioCyFwp8LaUfhq2/HltTe+IMF6TvSIxilEBUkN787nLeedwVsYI6JYHlwL4119D1urVZ+qMQD3WARxnlqJhYwd9f8Mo17ZQWhU02w0ycQLA5dwEIZR9VwMvUD9Y9wwWsS7zswDgB20ofTeaOjILNQJB52/SX2Tsq29/A7iaJdbudzKlq/BnzyL6ccpjae99nGMh1ActRtf2aHZmlq/lZ3e+pqyoBmtPXeJ4tiGp9QC0WNe3VxqFiSdzEPw21QSkJyejVW6kEBfMeKgfhMA45CPDapxzoqxbcdKy+oneHhCTYu1oIKTVDGf0j/dRjULD1m7dSwScD3w==~-1~||-1||~-1; AMCV_9D88879D5579828F7F000101%40AdobeOrg=1585540135%7CMCIDTS%7C18997%7CMCMID%7C54790893569662697931796380628130773111%7CMCAAMLH-1641979156%7C11%7CMCAAMB-1641979156%7CRKhpRz8krg2tLO6pguXWp5olkAcUniQYPHaMWWgdJ3xzPWQmdj0y%7CMCOPTOUT-1641381556s%7CNONE%7CvVersion%7C4.4.0; s_sq=%5B%5BB%5D%5D; bm_sv=B1635F20C5975979764D3569AE1ED450~diJG2rCuwUi+mBw89Kw9A4Xx1TYt24dBDQJgCfszRCk0kZiIo2o+ncqr4KqS+O3lyLKtxvkqXLzHBSzLJRgMVoohBD/UC+2tfdWJPVNLUYg7y5YoC8GveQGnOI31kKKKwqjllkKHq1/SIbwdIlNhW3OgOjbiyomMFcr2CaQ8sRE=',
            'Host': 'mydhl.express.dhl',
            'Referer': 'https://mydhl.express.dhl/hk/zh/tracking.html',
            'sec-ch-ua': '"Chromium";v="92", " Not A;Brand";v="99", "Google Chrome";v="92"',
            'sec-ch-ua-mobile': '?0',
            'Sec-Fetch-Dest': 'empty',
            'Sec-Fetch-Mode': 'cors',
            'Sec-Fetch-Site': 'same-origin',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36'
        }
        parm = {
            'AWB': '{}'.format(str(j)[:-2]),
            'countryCode': 'hk',
            'languageCode': 'zh',
        }
        res = requests.get(url, headers=headers, params=parm, verify=False).json()
        # print(res)
        for i in res['results'][0]['checkpoints']:
            print(i)
            times = i['date'] + ' ' + i['time']
            title = i['description']
            describes = i['location']
            Tracking_number = j
            try:
                pIds=''
                for o in i['pIds']:
                    pIds=pIds+','+o
                mysql_s(times, title, describes, Tracking_number, pIds)
            except KeyError as g:
                pIds = ''
                mysql_s(times, title, describes, Tracking_number, pIds)

if __name__ == '__main__':
    run()
